
'''
-- 创建设备接收数据表
CREATE TABLE device_data (
    id SERIAL PRIMARY KEY,
    device_id INT NOT NULL,
    data_type VARCHAR(50) NOT NULL,
    data_json JSONB NOT NULL,
    data_direction VARCHAR(20) NOT NULL, -- up 上传，down 下载
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id)
);

-- 单独创建普通索引（关键修正！）
CREATE INDEX idx_device_data_device_id ON device_data (device_id);
CREATE INDEX idx_device_time ON device_data (device_id, created_at);

-- 单独创建 GIN 索引
CREATE INDEX idx_data_value_gin ON device_data USING GIN (data_json);
SELECT SETVAL('device_data_id_seq',max(id),TRUE) FROM device_data;  

'''


from . import logger
import psycopg2

class DeviceDataTable:
    
    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 添加设备数据
    def create_device_data(self, device_id, data_type, data_json, data_direction): 
        '''data_json 可以是字典或列表'''
        try:
            sql = "INSERT INTO device_data (device_id, data_type, data_json, data_direction) VALUES (%s, %s, %s, %s)"
            values = (device_id, data_type, data_json, data_direction)
            self.cursor.execute(sql, values)
            return True

        except Exception as e:
            logger.error("Error inserting device data into database: {}".format(e))
            return False
    # 添加设备数据 by device_code
    def create_device_data_by_device_code(self, device_code, data_type, data_json, data_direction):
        '''data_json 可以是字典或列表'''
        try:
            sql = "INSERT INTO device_data (device_id, data_type, data_json, data_direction) VALUES ((SELECT id FROM devices WHERE device_code = %s), %s, %s, %s)"
            values = (device_code, data_type, data_json, data_direction)
            self.cursor.execute(sql, values)
            logger.debug(f"insert device data by device_code: {device_code}, data_type: {data_type}, data_json: {data_json}, data_direction: {data_direction}")
            return True

        except Exception as e:
            logger.error("Error inserting device data into database: {}".format(e))
            return False
    
    # 删除设备数据
    def delete_device_data(self, device_id_list):
        try:

            sql = "DELETE FROM device_data WHERE device_id IN %s"
            self.cursor.execute(sql, (tuple(device_id_list),))
            return True

        except Exception as e:
            logger.error("Error deleting device data from database: {}".format(e))
            return False
        

    
        

    # 获取设备数据
    def get_device_data(self, device_id=None, data_direction=None, date_start=None, date_end=None, limit=20, offset=0):

        if limit > 100:
            limit = 100

        if data_direction not in ['up', 'down']:

            data_direction = None

        if date_start and date_end and date_start > date_end:
    
            date_start = None
            date_end = None

        try:

            values = []

            sql = "SELECT * FROM device_data WHERE 1=1"

            if device_id:
                values.append(device_id)
                sql += " AND device_id = %s"

            if date_start:
                values.append(date_start)
                sql += " AND created_at >= %s"
            if date_end:
                values.append(date_end)
                sql += " AND created_at <= %s"

            if data_direction:
                values.append(data_direction)
                sql += " AND data_direction = %s"

            values.append(limit)
            values.append(offset)

            sql += " ORDER BY created_at DESC LIMIT %s OFFSET %s;"

            logger.debug("SQL: {}".format(sql))
            logger.debug("Values: {}".format(values))

            self.cursor.execute(sql, tuple(values))
            return True, self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting device data from database: {}".format(e))
            return False, []


    
    # 获取设备数据总数
    def get_device_data_count(self, device_id):
    
        try:
            sql = "SELECT COUNT(*) FROM device_data WHERE device_id = %s"
            self.cursor.execute(sql, (device_id,))
            return True, self.cursor.fetchone()[0]

        except Exception as e:
            logger.error("Error getting device data count from database: {}".format(e))
            return False, 0